id	title	description	subtitle
connecting-to-postgres	Connecting to your database	Explore the options for connecting to your Postgres database.	Explore the options for connecting to your Postgres database.

Supabase provides several options for programmatically connecting to your Postgres database:

Programmatic access using the Data APIs
Connection pooling for scalable connections
Direct connections using the built-in Postgres connection system

Direct database connections will start resolving to an IPv6 address. Starting from January 26, 2024, projects will be gradually migrated to this behavior. If you connect to your database using the REST or GraphQL endpoints, this doesn't affect you. If you use the database connection string, see the GitHub discussion on how to prepare for IPv6.

Data APIs

Supabase provides auto-updating Data APIs. These are the easiest way to get started if you are managing data (fetching, inserting, updating). We provide several types of API to suit your preferences:

REST: interact with your database through a REST interface.
GraphQL: interact with your database through a GraphQL interface.
Realtime: listen to database changes over websockets.

Connection pooler

A "connection pool" is a system (external to Postgres) which manages Postgres connections.

When a client makes a request, the pooler "allocates" an available connection to the client. When the client transaction or session is completed the connection is returned to the pool and is free to be used by another client.

Connecting to the database directly vs using a Connection Pooler

Every Supabase project comes with a connection pooler for managing connections to your database. The pooler provides 2 important services:

It manages connections for applications that connect and disconnect from the database frequently. For example, serverless functions and ORMs such as Prisma, Drizzle, and Kysely often make and drop connections to the database. If they connected directly each time, they would quickly exhaust your database server's memory. To connect to your database efficiently with such tools, you need a pooler.
It provides an IPv4 endpoint to connect to your database, which you can use if your network provider doesn't support IPv6. This is unlike the direct database connection, which resolves to an IPv6 address, unless you've enabled the IPv4 add-on.

The connection pooler is available in 2 modes: Transaction and Session.

Transaction mode is recommended if you are connecting from serverless environments. A connection is assigned to the client for the duration of a transaction. Two consecutive transactions from the same client can be executed over two different connections. Some session-based Postgres features such as prepared statements are not available with this option.

Session mode is similar to connecting to your database directly. There is full support for prepared statements in this mode. When a new client connects, a connection is assigned to the client until it disconnects. You might run into pooler connection limits since the connection is held till the client disconnects.

You can find the connection pool config in the Database settings inside the dashboard:

Go to the Settings section.
Click Database.
Under Connection string, make sure Use connection pooling is checked. Copy the URI.

Direct connections

You can also use a direct connection to connect directly to your Postgres database. By default, you can connect to it via IPv6, which isn't supported by all networks. If you need an IPv4 address, use the connection pooler instead or enable the IPv4 add-on.

You can find the direct connection string in the Database settings inside the dashboard:

Go to the Settings section.
Click Database.
Under Connection string, make sure Use connection pooling is unchecked. Copy the URI.

Choosing a connection method

Are you performing operations supported by the Data APIs? We recommend using the APIs.
Are you connecting to your database and then disconnecting immediately (for example, are you running your code in a serverless environment)? Use the connection pooler in transaction mode.
Are you on a network that doesn't support IPv6? Use the connection pooler. (If you need a long-lived connection, for example for prepared statements, use session mode.)
Are you connecting to your database and maintaining a connection, and does your network support IPv6? Use a direct connection.

Connecting with SSL

You should connect to your database using SSL wherever possible, to prevent snooping and man-in-the-middle attacks.

You can obtain your connection info and Server root certificate from your application's dashboard:

Integrations

Connecting with Drizzle

Drizzle ORM is a TypeScript ORM for SQL databases designed with maximum type safety in mind. You can use their ORM to connect to your database.

<StepHikeCompact.Step step={1}>

<StepHikeCompact.Details title="Install">

Install Drizzle and related dependencies.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

 ```shell
 npm i drizzle-orm postgres
 npm i -D drizzle-kit
 ```

</StepHikeCompact.Code>

</StepHikeCompact.Step>

<StepHikeCompact.Step step={2}>

<StepHikeCompact.Details title="Create your models">

Create a `schema.ts` file and define your models.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

```ts schema.ts
import { pgTable, serial, text, varchar } from "drizzle-orm/pg-core";

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: text('full_name'),
  phone: varchar('phone', { length: 256 }),
});
```

</StepHikeCompact.Code>

</StepHikeCompact.Step>

<StepHikeCompact.Step step={3}>

<StepHikeCompact.Details title="Connect">

Connect to your database using the Connection Pooler.

In your [`Database Settings`](https://supabase.com/dashboard/project/_/settings/database), make sure `Use connection pooler` is checked, then copy the URI and save it as the `DATABASE_URL` environment variable. Remember to replace the password placeholder with your actual database password.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

```ts db.ts
import 'dotenv/config'

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'

const connectionString = process.env.DATABASE_URL

// Disable prefetch as it is not supported for "Transaction" pool mode
export const client = postgres(connectionString, { prepare: false })
export const db = drizzle(client);
```

</StepHikeCompact.Code>

</StepHikeCompact.Step>

Connecting with pgAdmin

pgAdmin is a GUI tool for managing Postgres databases. You can use it to connect to your database via SSL:

<StepHikeCompact.Step step={1}>

<StepHikeCompact.Details title="Register">

Register a new Postgres server.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

<Image
  className="hidden dark:block"
  alt="Register a new postgres server."
  src="/docs/img/guides/database/connecting-to-postgres/pgadmin/register-server-pgAdmin.png?v=2"
/>
<Image
  className="dark:hidden"
  alt="Register a new postgres server."
  src="/docs/img/guides/database/connecting-to-postgres/pgadmin/register-server-pgAdmin--light.png"
/>

</StepHikeCompact.Code>

</StepHikeCompact.Step>

<StepHikeCompact.Step step={2}>

<StepHikeCompact.Details title="Name">

Name your server.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

 ![Name Postgres Server.](/docs/img/guides/database/connecting-to-postgres/pgadmin/name-pg-server.png)

</StepHikeCompact.Code>

</StepHikeCompact.Step>

<StepHikeCompact.Step step={3}>

<StepHikeCompact.Details title="Connect">

Add the connection info. Go to your [`Database Settings`](https://supabase.com/dashboard/project/_/settings/database). Make sure `Use connection pooling` is enabled. Switch the connection mode to `Session` and copy your connection parameters.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

 ![Add Connection Info.](/docs/img/guides/database/connecting-to-postgres/pgadmin/add-pg-server-conn-info.png)

</StepHikeCompact.Code>

</StepHikeCompact.Step>

<StepHikeCompact.Step step={4}>

<StepHikeCompact.Details title="SSL">

Navigate to the Parameters tab and select connection parameter as Root Certificate. Next navigate to the Root certificate input, it will open up a file-picker modal. Select the certificate you downloaded from your Supabase dashboard and save the server details. PgAdmin should now be able to connect to your Postgres via SSL.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

 ![Add Connection Info.](/docs/img/guides/database/connecting-to-postgres/pgadmin/database-settings-host.png)

</StepHikeCompact.Code>

</StepHikeCompact.Step>

Connecting with psql

psql is a command-line tool that comes with Postgres.

Download your SSL certificate to /path/to/prod-supabase.cer.

Find your connection settings. Go to your Database Settings and make sure Use connection pooling is checked. Change the connection mode to Session, and copy the parameters into the connection string:

psql "sslmode=verify-full sslrootcert=/path/to/prod-supabase.cer host=[CLOUD_PROVIDER]-0-[REGION].pooler.supabase.com dbname=postgres user=postgres.[PROJECT_REF]"

Connecting with Prisma

Refer to our Prisma integration guide for more details.

Connecting with Postgres.js

Postgres.js is a full-featured PostgreSQL client for Node.js and Deno.

<StepHikeCompact.Step step={1}>

<StepHikeCompact.Details title="Install">

Install Postgres.js and related dependencies.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

 ```shell
 npm i postgres
 ```

</StepHikeCompact.Code>

</StepHikeCompact.Step>

<StepHikeCompact.Step step={2}>

<StepHikeCompact.Details title="Connect">

Create a `db.js` file with the connection details.

To get your connection details, go to your [`Database Settings`](https://supabase.com/dashboard/project/_/settings/database). Make sure `Use connection pooling` is enabled. Choose `Transaction Mode` if you're on a platform with transient connections, such as a serverless function, and `Session Mode` if you have a long-lived connection. Copy the URI and save it as the environment variable `DATABASE_URL`.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

```ts
// db.js
import postgres from 'postgres'

const connectionString = process.env.DATABASE_URL
const sql = postgres(connectionString)

export default sql
```

</StepHikeCompact.Code>

</StepHikeCompact.Step>

<StepHikeCompact.Step step={3}>

<StepHikeCompact.Details title="Execute commands">

Use the connection to execute commands.

</StepHikeCompact.Details>

<StepHikeCompact.Code>

```ts
import sql from './db.js'

async function getUsersOver(age) {
  const users = await sql`
    select name, age
    from users
    where age > ${ age }
  `
  // users = Result [{ name: "Walter", age: 80 }, { name: 'Murray', age: 68 }, ...]
  return users
}
```

</StepHikeCompact.Code>

</StepHikeCompact.Step>

Troubleshooting Supavisor

Connection pooler logs are found here. The following are common errors and their solutions:

Max client connections reached This error happens when the number of connections to Supavisor is more than the allowed limit of your compute add-on. Upgrade the database to a higher compute add-on to increase the number of Supavisor connections.
Connection failed {:error, :eaddrnotavail} to 'db.xxx.supabase.co':5432 Supavisor cannot connect to the customer database. This is usually caused if the target database is unable to respond.
Connection failed {:error, :nxdomain} to 'db.xxx.supabase.co':5432 Supavisor cannot connect to the customer database. This is usually caused if the target database is unable to respond.
Connection closed when state was authentication This error happens when either the database doesn’t exist or if the user doesn't have the right credentials.
Subscribe error: {:error, :worker_not_found} This log event is emitted when the client tries to connect to the database, but Supavisor does not have the necessary information to route the connection. Try reconnecting to the database as it can take some time for the project information to propagate to Supavisor.
Subscribe error: {:error, {:badrpc, {:error, {:erpc, :timeout}}}} This is a timeout error when the communication between different Supavisor nodes takes longer than expected. Try reconnecting to the database.
Terminating with reason :client_termination when state was :busy This error happens when the client terminates the connection before the connection with the database is completed.

Finding your database hostname

Your database’s hostname is crucial for establishing a direct connection. It resolves to the underlying IP address of your database. To find your hostname, navigate to your Database Settings.

Note that the pooler (connection pooler) has a different IP than your database. To reveal the database host and direct connection string, hide the pooler connection string.

Example hostname: db.<PROJECT_REF>.supabase.co

Managing your IP address

To determine your current IP address, you can use an IP address lookup website or the terminal command:

nslookup db.<PROJECT_REF>.supabase.co

This command queries the domain name servers to find the IP address of the given hostname.

Example IPv6 Address: 2a05:d014:1c06:5f0c:d7a9:8616:bee2:30df

IPv6 address

Upon project creation, a static IPv6 address is assigned. This address might change when:

A project is paused or resumed.
The database is upgraded.

IPv4 address

Opting for the static IPv4 add-on provides a more stable connection address. The IPv4 address remains constant unless the project is paused or resumed.

Unlike the IPv6 address, upgrading your database doesn't affect the IPv4 address.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

connecting-to-postgres.mdx

connecting-to-postgres.mdx

Data APIs

Connection pooler

Direct connections

Choosing a connection method

Connecting with SSL

Integrations

Connecting with Drizzle

Connecting with pgAdmin

Connecting with psql

Connecting with Prisma

Connecting with Postgres.js

Troubleshooting Supavisor

Finding your database hostname

Managing your IP address

IPv6 address

IPv4 address

Files

connecting-to-postgres.mdx

Latest commit

History

connecting-to-postgres.mdx

File metadata and controls

Data APIs

Connection pooler

Direct connections

Choosing a connection method

Connecting with SSL

Integrations

Connecting with Drizzle

Connecting with pgAdmin

Connecting with psql

Connecting with Prisma

Connecting with Postgres.js

Troubleshooting Supavisor

Finding your database hostname

Managing your IP address

IPv6 address

IPv4 address